USE [PeDALS_DEV]
GO
/****** Object:  StoredProcedure [dbo].[PAC_CreateProvenance]    Script Date: 02/22/2010 15:20:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PAC_CreateProvenance]
	(@ProvenanceName VARCHAR (100),
	@ProvenanceAdministrativeHistory VARCHAR (MAX),
	@ProvenanceGeneralNote VARCHAR (MAX),
	@ProvenanceId INT OUTPUT)

AS
-- Start the transaction
BEGIN 
TRY
SET NOCOUNT ON
BEGIN TRANSACTION
-- Check if submitted provenance name is already in database
IF NOT EXISTS (SELECT ProvenanceName FROM Provenance WHERE ProvenanceName = LTRIM(RTRIM(@ProvenanceName)))
	BEGIN
	-- Create the new provenance and return the provenance id
		INSERT INTO [PeDALS_DEV].[dbo].[Provenance]
				   ([ProvenanceName]
				   ,[ProvenanceAdministrativeHistory]
				   ,[ProvenanceGeneralNote]
				   ,[CreatedDate]
				   ,[CreatedBy])
			 VALUES
				   (LTRIM(RTRIM(@ProvenanceName))
				   ,@ProvenanceAdministrativeHistory
				   ,@ProvenanceGeneralNote
					,GetDate()
					,'Cataloger')
			SET @ProvenanceId = SCOPE_IDENTITY()
	END
	COMMIT TRANSACTION

END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
		PRINT 'An error occurred.  Please notify your administrator to check the error database.'
		INSERT INTO [PEDALS_PAC_ERRORS].[PAC_ERROR](ErrorNumber, ErrorMessage, CreatedDate)
			VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH
